﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class LichLamViecDAO
    {
        public List<LichLamViecDTO> LayDanhSachLichLamViec()
        {
            List<LichLamViecDTO> listLLV = new List<LichLamViecDTO>();
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                String sql = "Select a.*,IIF(TinhTrang=1,N'Đã làm',N'Chưa làm')  as TinhTrang2,b.TenNV from LichLamViec a, NhanVien b WHERE a.MaNV = b.MaNV";

                conn = DataProvider.ConnectDB();
                SqlCommand cm = new SqlCommand(sql, conn);
                reader = cm.ExecuteReader();
                while (reader.Read())
                {
                    LichLamViecDTO llvDTO = new LichLamViecDTO();
                    llvDTO.MaLLV = reader.GetInt32(0);
                    llvDTO.ThoiGianLamViec = reader.GetTimeSpan(1);
                    llvDTO.NgayLamViec = reader.GetDateTime(2);
                    llvDTO.TinhTrang = reader.GetInt32(3);
                    llvDTO.MaNV = reader.GetInt32(4);
                    llvDTO.TinhTrang2 = reader.GetString(5);
                    llvDTO.TenNV = reader.GetString(6);
                    listLLV.Add(llvDTO);
                }
            }
            catch (Exception ex)
            {
                String e = ex.ToString();
            }
            finally
            { conn.Close(); }
            return listLLV;
        }

        public String ThemLichLamViec(LichLamViecDTO llvDTO)
        {
            String mess = "";
            SqlConnection conn = null;
            try
            {
                conn = DataProvider.ConnectDB();
                String sql = "INSERT INTO LichLamViec(ThoiGianLamViec,NgayLamViec,TinhTrang,MaNV)";
                sql += "VALUES(@ThoiGianLamViec,@NgayLamViec,@TinhTrang,@maNV)";
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                SqlParameter para;
                para = cmd.Parameters.Add("@ThoiGianLamViec", SqlDbType.Time);
                para.Value = llvDTO.ThoiGianLamViec;
                para = cmd.Parameters.Add("@ngayLamViec", SqlDbType.Date);
                para.Value = llvDTO.NgayLamViec;
                para = cmd.Parameters.Add("@TinhTrang", SqlDbType.Int);
                para.Value = llvDTO.TinhTrang;
                para = cmd.Parameters.Add("@MaNV", SqlDbType.Int);
                para.Value = llvDTO.MaNV;

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                mess = "Lỗi thêm lịch làm việc";
                String e = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
    }
}
